package com.cpi.cornpp.versionsvalues.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.versionsvalues.dto.VersionsValuesDTO;
import com.cpi.cornpp.versionsvalues.exception.VersionsValuesException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;
/**
 * 
 * @description: This Class is used to handle the Versions Values Data Access
 * @author Edgar Joao
 */
public class VersionsValuesDAO extends DBDAO{
	/** SQL List Versions Values */
	private static final String SQL_LIST_VERSIONS_VALUES = "SELECT version_value_id, description,isnull(  numeric_value, -1 ) as numeric_value, string_value FROM versions_values WHERE version_id = ?";//$NON-NLS-1$
	/** SQL Update Versions Values */
	private static final String UPDATE_VERSIONS_VALUES = "UPDATE versions_values SET numeric_value = ?, string_value = ? WHERE version_id = ? AND version_value_id = ?";//$NON-NLS-1$
	/**
	 * Get Versions Values List
	 * @param versionId
	 * @return
	 * @throws VersionsValuesException
	 */
	public List list(int versionId) throws VersionsValuesException{
		List list = null;
		VersionsValuesDTO dto = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
			list = new ArrayList(0);
			preparedStatement = prepareSQL(SQL_LIST_VERSIONS_VALUES, dsName);
			preparedStatement.setInt(1, versionId);
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				dto = new VersionsValuesDTO();
				dto.setVersionValueId(rs.getString(VersionsValuesDTO.VERSION_VALUE_ID));
				dto.setDescription(rs.getString(VersionsValuesDTO.DESCRIPTION));
				dto.setNumericValue(rs.getDouble(VersionsValuesDTO.NUMERIC_VALUE));
				dto.setStringValue(rs.getString(VersionsValuesDTO.STRING_VALUE));
				list.add(dto);
			}			
		}catch (PoolException poolException){
			VersionsValuesException versionsValuesException = null;
			poolException.printStackTrace(System.out);
			versionsValuesException = new VersionsValuesException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw versionsValuesException;	
		} catch (SQLException sqlException) {
			VersionsValuesException versionsValuesException = null;
			sqlException.printStackTrace(System.out);
			versionsValuesException = new VersionsValuesException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw versionsValuesException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return list;
	}
	/**
	 * Save Versions Values
	 * @param versionId
	 * @throws VersionsValuesException
	 */
	public void save(int versionId, VersionsValuesDTO dto) throws VersionsValuesException{
		PreparedStatement preparedStatement = null;		
		try{
			preparedStatement = prepareSQL(UPDATE_VERSIONS_VALUES, dsName);
			if( dto.getNumericValue() == -1 ){
				preparedStatement.setNull(1, Types.INTEGER);
			}else{
				preparedStatement.setDouble(1, dto.getNumericValue());
			}
			if(  StringUtils.isEmpty(dto.getStringValue())  ){
				preparedStatement.setNull(2, Types.VARCHAR);
			}else{
				preparedStatement.setString(2,dto.getStringValue());
			}
			preparedStatement.setInt(3, versionId);
			preparedStatement.setString(4, dto.getVersionValueId());
			executeInsert(preparedStatement);	
		}catch (PoolException poolException){
			VersionsValuesException versionsValuesException = null;
			poolException.printStackTrace(System.out);
			versionsValuesException = new VersionsValuesException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw versionsValuesException;	
		} catch (SQLException sqlException) {
			VersionsValuesException versionsValuesException = null;
			sqlException.printStackTrace(System.out);
			versionsValuesException = new VersionsValuesException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw versionsValuesException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
}
